﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using Entity;
using System.Data.SqlClient;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;


namespace DAL
{
    public class EstudanteWrapper : BaseWrapper, IWrapper<Estudante>
    {
        public List<Estudante> GetAll()
        {
            List<Estudante> LISTA = new List<Estudante>();

            SqlConnection cn = new SqlConnection(ConnectionString());
            SqlCommand cmd = new SqlCommand("SP_ESTUDANTE_GETALL", cn);
            cmd.CommandType = CommandType.StoredProcedure;


            try
            {
                cn.Open();
                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    Estudante Est = new Estudante();

                    Est.IDESTUDANTE = int.Parse(dr["IDESTUDANTE"].ToString());
                    Est.IDUSUARIO = int.Parse(dr["IDUSUARIO"].ToString());
                    Est.NOME = dr["NOME"].ToString();
                    Est.RG = dr["RG"].ToString();
                    Est.CPF = dr["CPF"].ToString();
                    Est.DATANASCIMENTO = DateTime.Parse(dr["DATANASCIMENTO"].ToString());
                    Est.DATACADASTRO = DateTime.Parse(dr["DATACADASTRO"].ToString());
                    Est.IDENDERECO = int.Parse(dr["IDENDERECO"].ToString());

                    LISTA.Add(Est);
                }
            }
            catch (SqlException ex)
            {
                Messages += "Erro na inserção de dados\n";
                Messages += ex.Message.ToString();
                Logs.MENSAGEM = Messages;
                Logs.TIPOLOG = "Leitura de dados";
                LOGsWrapper LogsWrapper = new LOGsWrapper();
                LogsWrapper.Insert(Logs);
            }
            finally
            {
                ;
                cn.Close();
            }
            return LISTA;

        }

        public Estudante GetById(int id)
        {
            Estudante Est = new Estudante();

            SqlConnection cn = new SqlConnection(ConnectionString());
            SqlCommand cmd = new SqlCommand("SP_ESTUDANTE_GETBYID", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@IDESTUDANTE", id);

            SqlDataReader dr = cmd.ExecuteReader();

            try
            {
                if (dr.Read())
                {

                    Est.IDESTUDANTE = int.Parse(dr["IDESTUDANTE"].ToString());
                    Est.IDUSUARIO = int.Parse(dr["IDUSUARIO"].ToString());
                    Est.NOME = dr["NOME"].ToString();
                    Est.RG = dr["RG"].ToString();
                    Est.CPF = dr["CPF"].ToString();
                    Est.DATANASCIMENTO = DateTime.Parse(dr["DATANASCIMENTO"].ToString());
                    Est.DATACADASTRO = DateTime.Parse(dr["DATACADASTRO"].ToString());
                    Est.IDENDERECO = int.Parse(dr["IDENDERECO"].ToString());

                }
            }
            catch (SqlException ex)
            {
                Messages += "Erro na inserção de dados\n";
                Messages += ex.Message.ToString();
                Logs.MENSAGEM = Messages;
                Logs.TIPOLOG = "Leitura de dados";
                LOGsWrapper LogsWrapper = new LOGsWrapper();
                LogsWrapper.Insert(Logs);
            }
            finally
            {
                ;
                cn.Close();
            }
            return Est;
        }

        public bool Insert(Estudante entity)
        {
            bool status = false;

            SqlConnection cn = new SqlConnection(ConnectionString());
            SqlCommand cmd = new SqlCommand("SP_ESTUDANTE_INSERT", cn);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter parametro = new SqlParameter("@IDESTUDANTE", DbType.UInt32);
            parametro.Direction = ParameterDirection.Output;

            cmd.Parameters.Add(parametro);
   


            cmd.Parameters.AddWithValue("@IDUSUARIO", entity.IDUSUARIO);
            cmd.Parameters.AddWithValue("@NOME", entity.NOME);
            cmd.Parameters.AddWithValue("@RG", entity.RG);
            cmd.Parameters.AddWithValue("@CPF", entity.CPF);
            cmd.Parameters.AddWithValue("@DATANASCIMENTO", entity.DATANASCIMENTO);
            cmd.Parameters.AddWithValue("@IDENDERECO", entity.IDENDERECO);

            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();
                    status = true;
         entity.IDESTUDANTE = int.Parse(cmd.Parameters[0].Value.ToString());
            }
            catch (SqlException ex)
            {
                Messages += "Erro na inserção de dados\n";
                Messages += ex.Message.ToString();
                Logs.MENSAGEM = Messages;
                Logs.TIPOLOG = "Manipulação de dados";
                LOGsWrapper LogsWrapper = new LOGsWrapper();
                LogsWrapper.Insert(Logs);
            }
            finally
            {
                if (cn.State != ConnectionState.Closed)
                    cn.Close();
            }

            return status;

        }

        public bool Update(Estudante entity)
        {
            bool status = false;

            SqlConnection cn = new SqlConnection(ConnectionString());
            SqlCommand cmd = new SqlCommand("SP_ESTUDANTE_UPDATE", cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@IDESTUDANTE", entity.IDESTUDANTE);
            cmd.Parameters.AddWithValue("@IDUSUARIO", entity.IDUSUARIO);
            cmd.Parameters.AddWithValue("@NOME", entity.NOME);
            cmd.Parameters.AddWithValue("@RG", entity.RG);
            cmd.Parameters.AddWithValue("@CPF", entity.CPF);
            cmd.Parameters.AddWithValue("@DATANASCIMENTO", entity.DATANASCIMENTO);

            cmd.Parameters.AddWithValue("@IDENDERECO", entity.IDENDERECO);

            try
            {
                cn.Open();
                if (cmd.ExecuteNonQuery() > 0)
                    status = true;

            }
            catch (SqlException ex)
            {
                Messages += "Erro na inserção de dados\n";
                Messages += ex.Message.ToString();
                Logs.MENSAGEM = Messages;
                Logs.TIPOLOG = "Manipulação de dados";
                LOGsWrapper LogsWrapper = new LOGsWrapper();
                LogsWrapper.Insert(Logs);
            }
            finally
            {
                if (cn.State != ConnectionState.Closed)
                    cn.Close();
            }

            return status;
        }

        public bool Delete(Estudante entity)
        {
            throw new NotImplementedException();
        }

        public Estudante GetByIdUsuario(int id)
        {
            Estudante Est = new Estudante();

            SqlConnection cn = new SqlConnection(ConnectionString());
            SqlCommand cmd = new SqlCommand("SP_ESTUDANTE_GETBYIDUSUARIO", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@IDUSUARIO", id);

    

            try
            {
                cn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {

                    Est.IDESTUDANTE = int.Parse(dr["IDESTUDANTE"].ToString());
                    Est.IDUSUARIO = int.Parse(dr["IDUSUARIO"].ToString());
                    Est.NOME = dr["NOME"].ToString();
                    Est.RG = dr["RG"].ToString();
                    Est.CPF = dr["CPF"].ToString();
                    Est.DATANASCIMENTO = DateTime.Parse(dr["DATANASCIMENTO"].ToString());
                    Est.DATACADASTRO = DateTime.Parse(dr["DATACADASTRO"].ToString());
                    Est.IDENDERECO = int.Parse(dr["IDENDERECO"].ToString());

                }
            }
            catch (SqlException ex)
            {
                Messages += "Erro na inserção de dados\n";
                Messages += ex.Message.ToString();
                Logs.MENSAGEM = Messages;
                Logs.TIPOLOG = "Manipulação de dados";
                LOGsWrapper LogsWrapper = new LOGsWrapper();
                LogsWrapper.Insert(Logs);
            }
            finally
            {
                cn.Close();
            }
            return Est;
        }
    }
}
